﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using StudentProject.Model;
using System.Data.SqlClient;

namespace StudentProject.DAL
{
    public class MajorServiceDal
    {
        //查询并显示Major表
        public List<Major> GetLsit()
        {
            string sql = "select * from Major";
            //使用helper进行查询，得到结果
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            //将dt中的数据转存到list中
            List<Major> list = new List<Major>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new Major()
                {
                    ID = row["ID"].ToString(),
                    Name = row["Name"].ToString(),
                    
                });
            }
            //将集合返回
            return list;
        }

        //删除数据
        public int Remove(string pid)
        {
            //构造删除的sql语句
            string sql = "delete from Major where ID=@id";
            //根据语句构造参数
            SqlParameter ps = new SqlParameter("@id", pid);
            //执行操作
            return SqlHelper.ExecuteNonQuery(sql,ps);
        }

        //修改Major表
        public int UpdateInfo(Major major)
        {
            string sql = "update Major set  Name=@Name where ID=@ID";
            SqlParameter[] pars =
            {
                new SqlParameter("@ID",major.ID),
                new SqlParameter("@Name",major.Name)
            };

            //执行语句并返回结果
            return SqlHelper.ExecuteNonQuery(sql, pars);
        }

        //修改Major表(显示)
        public Major LoadUpMajor(string pid)
        {
            string sql = "select * from Major where ID=@pid";
            SqlParameter ps = new SqlParameter("@pid", pid);
            Major major = null;
            SqlDataReader dr = SqlHelper.ExecuteReader(sql, ps);
            if (dr.HasRows)//是否有行
            {
                while (dr.Read())
                {
                    major = new Major();
                    major.ID = dr.GetString(0);
                    major.Name = dr.GetString(1);
                   
                }
            }
            return major;
        }

        //添加Major表
        public int AddInfo(Major major)
        {
            //添加的SQL语句
            string sql = "insert into Major(ID,Name) values(@ID,@Name)";
            //SQL语句的参数
            SqlParameter[] ps =
            {
                new SqlParameter("@ID",major.ID),
                new SqlParameter("@Name",major.Name)
            };
            //返回操作
            return SqlHelper.ExecuteNonQuery(sql,ps);
        }
    }
}
